import pymysql

from utils.logging_tool.recordlog import logs
from config.operationConfig import OperationConfig

conf = OperationConfig()


# 连接MySQL数据库
class ConnectMysql:
    # 构造函数,初始化数据库连接
    def __init__(self, database_name=None):
        try:
            self.database_name = database_name
            # 从yaml文件中读取数据库，默认 pid
            if database_name == conf.get_section_mysql_uams('database'):
                self.mysql_conf_uams = {
                    'host': conf.get_section_mysql_uams('host'),
                    'port': int(conf.get_section_mysql_uams('port')),
                    'user': conf.get_section_mysql_uams('username'),
                    'password': conf.get_section_mysql_uams('password'),
                    'database': conf.get_section_mysql_uams('database')
                }
                try:
                    # 建立数据库连接
                    self.conn = pymysql.connect(**self.mysql_conf_uams, charset='utf8')
                    # 创建游标对象 cursor=pymysql.cursors.DictCursor，将数据库表字段显示，以key-value形式展示
                    self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
                    logs.info("""成功连接到MySQL数据库:{host}:{port}/{database}""".format(**self.mysql_conf_uams))
                except Exception as e:
                    logs.error(f'连接数据库失败，{e}')
            elif database_name == conf.get_section_mysql_pid('database') or database_name == None:
                self.mysql_conf_pid = {
                    'host': conf.get_section_mysql_pid('host'),
                    'port': int(conf.get_section_mysql_pid('port')),
                    'user': conf.get_section_mysql_pid('username'),
                    'password': conf.get_section_mysql_pid('password'),
                    'database': conf.get_section_mysql_pid('database')
                }
                try:
                    self.conn = pymysql.connect(**self.mysql_conf_pid, charset='utf8')
                    self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
                    logs.info("""成功连接到MySQL数据库:{host}:{port}/{database}""".format(**self.mysql_conf_pid))
                except Exception as e:
                    logs.error(f'连接数据库失败，{e}')
            else:
                logs.error("只支持系统配置的数据库名，请检查配置是否正确")
        except Exception as e:
            logs.error(f'err={e}')

    @classmethod
    def _remove_sql_comments(cls, sql_script):
        """
        定义正则表达式模式，用于匹配sql文件注释
        :param sql_script: 需要替换的语句
        :return: 返回替换后的结果
        """
        # # 去除块注释
        # comment_pattern_piece = re.compile(r"/\*[\s\S]*?\*/")
        # sql_script = re.sub(comment_pattern_piece, '', sql_script)  # 使用空字符串进行替换
        # 去除行注释
        # comment_pattern_line = re.compile('--.*\\n?')
        # sql_script = re.sub(comment_pattern_line, '', sql_script)  # 使用空字符串进行替换

        return sql_script

    def execute_sqls_file(self, file_path):
        """
         读取sql文件
        :param file_path: .sql 文件路径
        :return:
        """
        logs.info(f"开始执行sql文件：{file_path}")
        with self.conn:
            with self.cursor as cursor:
                # 读取 sql 文件， 保持为一个字符串列表
                with open(file_path, "r", encoding="utf-8") as f:
                    data = f.read()
                    # 以分号； 和换行符 进行切割
                    script_parts = data.split(";\n")
                    for part in script_parts:
                        part = self._remove_sql_comments(part)
                        try:
                            cursor.execute(part)
                            self.conn.commit()
                        except Exception as e:
                            logs.error(f"sql语句执行失败！\n  ---> sql：{part[:100]}\n  ---> err：{e}\n")
                            continue

    def execute_sqls(self, sqls):
        """
        执行多条sql语句（字符串内的SQL语句）
        :param sqls: 多条sql语句,以分号'；' 和空格结尾
        :return:
        """
        if sqls is None:
            logs.info("sql语句为空")
        else:
            script_parts = sqls.split("; ")  # 以分号； 和 空格 进行切割,切割后变成列表
            try:
                for part in script_parts:
                    part = self._remove_sql_comments(part)
                    logs.info(f"执行sql语句：{part}")
                    self.cursor.execute(part)
                    self.conn.commit()
                    logs.info(f"sql语句执行成功")
            except Exception as e:
                logs.error(f"sql语句执行失败！err= {e}")
            finally:
                self.close()

    def execute_sql(self, sql):
        """
        执行单条sql语句（字符串内的SQL语句）
        :param sql: sql语句
        :return:
        """
        if sql is None:
            logs.info("sql语句为空")
        else:
            try:
                sql = self._remove_sql_comments(sql)
                logs.info(f"执行sql语句：{sql}")
                self.cursor.execute(sql)
                self.conn.commit()
                logs.info(f"sql语句执行成功")
            except Exception as e:
                logs.error(f"sql语句执行失败！err= {e}")
            finally:
                self.close()

    def close(self):
        """
        关闭数据库连接
        :return:
        """
        # 如果数据打开，则关闭；否则没有操作
        if self.conn and self.cursor:
            # 关闭游标对象
            self.cursor.close()
            # 关闭数据库连接
            self.conn.close()
            logs.info('关闭MySQL数据库连接')
        return True


if __name__ == '__main__':
    db = ConnectMysql('uams')
    # sql = "SELECT * FROM pidcad_user"
    sql = "SELECT * FROM sys_users WHERE `NAME` = '超级管理员'"
